import psycopg2
from psycopg2 import sql
import os
from datetime import datetime, date, time

class HospitalDatabase:
    """
    Streamlined database connection class.
    This class only handles:
    1. Database connection
    2. Executing SQL files for setup
    3. CRUD operations for application use
    
    Database schema creation is handled by SQL files in the sql/ directory.
    """
    
    def __init__(self, host="localhost", port="5433", user="postgres", password="788690738", database="hospital_data"):
        self.connection_params = {
            'host': host,
            'port': port,
            'user': user,
            'password': password,
            'database': database
        }
        self.conn = None
        self.cursor = None
        self.sql_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'sql')

    def connect(self):
        """Connect to PostgreSQL database"""
        try:
            # First connect to default postgres database to create our database if needed
            temp_params = self.connection_params.copy()
            temp_params['database'] = 'postgres'
            temp_conn = psycopg2.connect(**temp_params)
            temp_conn.autocommit = True
            temp_cursor = temp_conn.cursor()
            
            # Create database if it doesn't exist
            temp_cursor.execute(f"SELECT 1 FROM pg_database WHERE datname = '{self.connection_params['database']}'")
            if not temp_cursor.fetchone():
                temp_cursor.execute(f"CREATE DATABASE {self.connection_params['database']}")
                print(f"DATABASE {self.connection_params['database']} CREATED SUCCESSFULLY")
            
            temp_cursor.close()
            temp_conn.close()
            
            # Now connect to our database
            self.conn = psycopg2.connect(**self.connection_params)
            self.cursor = self.conn.cursor()
            print("DATABASE CONNECTION SUCCESSFUL")
            return True
        except Exception as e:
            print(f"Connection failed: {e}")
            return False

    def execute_sql_file(self, filename):
        """Execute a SQL file"""
        try:
            filepath = os.path.join(self.sql_dir, filename)
            if not os.path.exists(filepath):
                print(f"SQL file not found: {filepath}")
                return False
            
            with open(filepath, 'r', encoding='utf-8') as f:
                sql_script = f.read()
            
            self.cursor.execute(sql_script)
            self.conn.commit()
            print(f"Successfully executed: {filename}")
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error executing {filename}: {e}")
            return False

    def setup_database(self):
        """Setup database by executing SQL files in order"""
        try:
            sql_files = [
                '01_create_tables.sql',
                '02_insert_sample_data.sql',
                '03_create_indexes.sql',
                '04_create_functions_triggers.sql'
            ]
            
            for sql_file in sql_files:
                if not self.execute_sql_file(sql_file):
                    print(f"Failed to execute {sql_file}")
                    return False
            
            print("DATABASE SETUP COMPLETED SUCCESSFULLY")
            return True
        except Exception as e:
            print(f"Database setup failed: {e}")
            return False

    # ===================== PATIENT OPERATIONS =====================
    
    def add_patient(self, first_name, last_name, date_of_birth, gender, blood_group, address, email, phone, 
                   emergency_contact=None, emergency_phone=None, medical_history=None):
        """Add a new patient to the database"""
        try:
            query = """
                INSERT INTO patients (first_name, last_name, date_of_birth, gender, blood_group, 
                                    address, email, phone, emergency_contact, emergency_phone, medical_history)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING patient_id
            """
            self.cursor.execute(query, (first_name, last_name, date_of_birth, gender, blood_group, 
                                       address, email, phone, emergency_contact, emergency_phone, medical_history))
            patient_id = self.cursor.fetchone()[0]
            self.conn.commit()
            return patient_id
        except Exception as e:
            self.conn.rollback()
            print(f"Error adding patient: {e}")
            return None

    def get_patient(self, patient_id):
        """Get patient by ID"""
        try:
            query = "SELECT * FROM patients WHERE patient_id = %s"
            self.cursor.execute(query, (patient_id,))
            return self.cursor.fetchone()
        except Exception as e:
            print(f"Error getting patient: {e}")
            return None

    def search_patients(self, search_term):
        """Search patients by name, email, or phone"""
        try:
            query = """
                SELECT * FROM patients 
                WHERE first_name ILIKE %s 
                OR last_name ILIKE %s 
                OR email ILIKE %s 
                OR phone ILIKE %s
                ORDER BY last_name, first_name
            """
            search_pattern = f"%{search_term}%"
            self.cursor.execute(query, (search_pattern, search_pattern, search_pattern, search_pattern))
            return self.cursor.fetchall()
        except Exception as e:
            print(f"Error searching patients: {e}")
            return []

    def update_patient(self, patient_id, **kwargs):
        """Update patient information"""
        try:
            # Build dynamic UPDATE query
            set_clauses = []
            values = []
            
            for key, value in kwargs.items():
                set_clauses.append(f"{key} = %s")
                values.append(value)
            
            if not set_clauses:
                return False
            
            values.append(patient_id)
            query = f"UPDATE patients SET {', '.join(set_clauses)} WHERE patient_id = %s"
            
            self.cursor.execute(query, values)
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error updating patient: {e}")
            return False

    def delete_patient(self, patient_id):
        """Delete a patient from the database"""
        try:
            query = "DELETE FROM patients WHERE patient_id = %s"
            self.cursor.execute(query, (patient_id,))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error deleting patient: {e}")
            return False

    def get_all_patients(self):
        """Get all patients"""
        try:
            query = "SELECT * FROM patients ORDER BY patient_id"
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except Exception as e:
            print(f"Error getting all patients: {e}")
            return []

    # ===================== ROOM OPERATIONS =====================

    def get_all_rooms(self):
        """Get all rooms"""
        try:
            query = "SELECT * FROM rooms ORDER BY room_number"
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except Exception as e:
            print(f"Error getting all rooms: {e}")
            return []

    def get_room(self, room_number):
        """Get room by number"""
        try:
            query = "SELECT * FROM rooms WHERE room_number = %s"
            self.cursor.execute(query, (room_number,))
            return self.cursor.fetchone()
        except Exception as e:
            print(f"Error getting room: {e}")
            return None

    def add_room(self, room_number, room_type, capacity, rate_per_day, floor_number=None, dept_id=None):
        """Add a new room"""
        try:
            query = """
                INSERT INTO rooms (room_number, room_type, capacity, rate_per_day, floor_number, dept_id)
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            self.cursor.execute(query, (room_number, room_type, capacity, rate_per_day, floor_number, dept_id))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error adding room: {e}")
            return False

    def update_room(self, room_number, **kwargs):
        """Update room information"""
        try:
            set_clauses = []
            values = []
            
            for key, value in kwargs.items():
                set_clauses.append(f"{key} = %s")
                values.append(value)
            
            if not set_clauses:
                return False
            
            values.append(room_number)
            query = f"UPDATE rooms SET {', '.join(set_clauses)} WHERE room_number = %s"
            
            self.cursor.execute(query, values)
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error updating room: {e}")
            return False

    def delete_room(self, room_number):
        """Delete a room"""
        try:
            query = "DELETE FROM rooms WHERE room_number = %s"
            self.cursor.execute(query, (room_number,))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error deleting room: {e}")
            return False

    def assign_room_to_patient(self, room_number, patient_id):
        """Assign a room to a patient"""
        try:
            query = "UPDATE rooms SET patient_id = %s, status = 'occupied', admission_date = CURRENT_DATE WHERE room_number = %s"
            self.cursor.execute(query, (patient_id, room_number))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error assigning room: {e}")
            return False

    # ===================== MEDICAL STAFF OPERATIONS =====================

    def get_all_medical_staff(self):
        """Get all medical staff"""
        try:
            query = "SELECT * FROM medical_staff ORDER BY staff_id"
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except Exception as e:
            print(f"Error getting all medical staff: {e}")
            return []

    def get_medical_staff(self, staff_id):
        """Get medical staff by ID"""
        try:
            query = "SELECT * FROM medical_staff WHERE staff_id = %s"
            self.cursor.execute(query, (staff_id,))
            return self.cursor.fetchone()
        except Exception as e:
            print(f"Error getting medical staff: {e}")
            return None

    def add_medical_staff(self, staff_id, first_name, last_name, email, phone, specialization, dept_id, salary, license_number=None):
        """Add a new medical staff member"""
        try:
            query = """
                INSERT INTO medical_staff (staff_id, first_name, last_name, email, phone, 
                                          specialization, dept_id, salary, license_number)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            self.cursor.execute(query, (staff_id, first_name, last_name, email, phone, 
                                       specialization, dept_id, salary, license_number))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error adding medical staff: {e}")
            return False

    def update_medical_staff(self, staff_id, **kwargs):
        """Update medical staff information"""
        try:
            set_clauses = []
            values = []
            
            for key, value in kwargs.items():
                set_clauses.append(f"{key} = %s")
                values.append(value)
            
            if not set_clauses:
                return False
            
            values.append(staff_id)
            query = f"UPDATE medical_staff SET {', '.join(set_clauses)} WHERE staff_id = %s"
            
            self.cursor.execute(query, values)
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error updating medical staff: {e}")
            return False

    def delete_medical_staff(self, staff_id):
        """Delete a medical staff member"""
        try:
            query = "DELETE FROM medical_staff WHERE staff_id = %s"
            self.cursor.execute(query, (staff_id,))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error deleting medical staff: {e}")
            return False

    # ===================== APPOINTMENT OPERATIONS =====================

    def get_all_appointments(self):
        """Get all appointments"""
        try:
            query = "SELECT * FROM appointments ORDER BY appointment_date, appointment_time"
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except Exception as e:
            print(f"Error getting all appointments: {e}")
            return []

    def get_appointment(self, appointment_id):
        """Get appointment by ID"""
        try:
            query = "SELECT * FROM appointments WHERE appointment_id = %s"
            self.cursor.execute(query, (appointment_id,))
            return self.cursor.fetchone()
        except Exception as e:
            print(f"Error getting appointment: {e}")
            return None

    def add_appointment(self, patient_id, staff_id, appointment_date, appointment_time, duration_minutes=30, notes=None):
        """Add a new appointment"""
        try:
            query = """
                INSERT INTO appointments (patient_id, staff_id, appointment_date, appointment_time, 
                                        duration_minutes, notes)
                VALUES (%s, %s, %s, %s, %s, %s)
                RETURNING appointment_id
            """
            self.cursor.execute(query, (patient_id, staff_id, appointment_date, appointment_time, 
                                       duration_minutes, notes))
            appointment_id = self.cursor.fetchone()[0]
            self.conn.commit()
            return appointment_id
        except Exception as e:
            self.conn.rollback()
            print(f"Error adding appointment: {e}")
            return None

    def update_appointment(self, appointment_id, **kwargs):
        """Update appointment information"""
        try:
            set_clauses = []
            values = []
            
            for key, value in kwargs.items():
                set_clauses.append(f"{key} = %s")
                values.append(value)
            
            if not set_clauses:
                return False
            
            values.append(appointment_id)
            query = f"UPDATE appointments SET {', '.join(set_clauses)} WHERE appointment_id = %s"
            
            self.cursor.execute(query, values)
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error updating appointment: {e}")
            return False

    def delete_appointment(self, appointment_id):
        """Delete an appointment"""
        try:
            query = "DELETE FROM appointments WHERE appointment_id = %s"
            self.cursor.execute(query, (appointment_id,))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error deleting appointment: {e}")
            return False

    # ===================== INVOICE OPERATIONS =====================

    def get_all_invoices(self):
        """Get all invoices"""
        try:
            query = "SELECT * FROM invoices ORDER BY invoice_date DESC"
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except Exception as e:
            print(f"Error getting all invoices: {e}")
            return []

    def get_invoice(self, invoice_id):
        """Get invoice by ID"""
        try:
            query = "SELECT * FROM invoices WHERE invoice_id = %s"
            self.cursor.execute(query, (invoice_id,))
            return self.cursor.fetchone()
        except Exception as e:
            print(f"Error getting invoice: {e}")
            return None

    def add_invoice(self, patient_id, appointment_id, invoice_date, due_date, subtotal, tax_amount, total_amount, status='pending'):
        """Add a new invoice"""
        try:
            query = """
                INSERT INTO invoices (patient_id, appointment_id, invoice_date, due_date, 
                                    subtotal, tax_amount, total_amount, status)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING invoice_id
            """
            self.cursor.execute(query, (patient_id, appointment_id, invoice_date, due_date, 
                                       subtotal, tax_amount, total_amount, status))
            invoice_id = self.cursor.fetchone()[0]
            self.conn.commit()
            return invoice_id
        except Exception as e:
            self.conn.rollback()
            print(f"Error adding invoice: {e}")
            return None

    def update_invoice(self, invoice_id, **kwargs):
        """Update invoice information"""
        try:
            set_clauses = []
            values = []
            
            for key, value in kwargs.items():
                set_clauses.append(f"{key} = %s")
                values.append(value)
            
            if not set_clauses:
                return False
            
            values.append(invoice_id)
            query = f"UPDATE invoices SET {', '.join(set_clauses)} WHERE invoice_id = %s"
            
            self.cursor.execute(query, values)
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error updating invoice: {e}")
            return False

    def delete_invoice(self, invoice_id):
        """Delete an invoice"""
        try:
            query = "DELETE FROM invoices WHERE invoice_id = %s"
            self.cursor.execute(query, (invoice_id,))
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"Error deleting invoice: {e}")
            return False

    # ===================== UTILITY OPERATIONS =====================

    def close_connection(self):
        """Close database connection"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
        print("Database connection closed")
